﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Tool
{
    /// <summary>
    /// 此类用sql拼接,再去执行查询,
    /// 其实还有一种写法就是用缓存,仿照php写法
    /// </summary>
    public class DBSentence
    {

        public string filed(string fileds)
        {
            return "Select "+fileds;
        }

        public string tableName(string table)
        {
            return " from " + table;
        }

        public string where(string where)
        {
            return " where " + where;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filed"></param>
        /// <param name="Table"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public string Select(string filed,string Table, string where)
            {
                string str = "";

                str += "select "+ filed + " from "+ Table+" where "+ where;

                return str;
            }

            /// <summary>
            /// 连接拼接
            /// </summary>
            /// <param name="Table">表名</param>
            /// <param name="alias">别名</param>
            /// <param name="where">条件</param>
            /// <param name="method">连接方式(left\right)</param>
            /// <returns></returns>
            public string Join(string Table, string where, string method)
            {
                string str = "";

                str +=" "+ method + " join " + Table + " on " + where;

                return str;
            }

            /// <summary>
            /// 分页
            /// </summary>
            /// <returns></returns>
            public string PageLimit(int page, int pageSize)
            {
                string str = "";
                int firstSize = (page - 1) * pageSize + 1;
                int endSize = page * pageSize;
                str += "between " + firstSize + " and " + endSize;
                return str;
            
            }

            /// <summary>
            /// 排序,默认asc
            /// </summary>
            /// <param name="filed">要排序的字段</param>
            /// <param name="sort"></param>
            /// <returns></returns>
            public string OrderBy(string filed, string sort = " asc ")
            {
                return "OrderBy " + filed + sort;
            }

        /// <summary>
        /// 根据实体类得到表结构
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public DataTable CreateData<T>(T model)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (propertyInfo.Name != "CTimestamp")//些字段为oracle中的Timesstarmp类型
                {
                    dataTable.Columns.Add(new DataColumn(propertyInfo.Name, propertyInfo.PropertyType));
                }
                else
                {
                    dataTable.Columns.Add(new DataColumn(propertyInfo.Name, typeof(DateTime)));
                }
            }
            return dataTable;
        }

        public static DataTable ListToDataTable<T>(List<T> entitys)
        {

            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                return new DataTable();
            }

            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable("dt");
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }

            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);

                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }
    }

    
}
